package com.china08.yxyapi.service.db.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.china08.yxyapi.entity.db.lessons.YxyLessonsSynchroLesson;
import com.china08.yxyapi.entity.db.lessons.YxyLessonsTopicLesson;
import com.china08.yxyapi.repository.db.YxyLessonsSynchroLessonRepository;
import com.china08.yxyapi.repository.db.YxyLessonsTopicLessonRepository;
import com.china08.yxyapi.service.db.YxyKeChengService;

@Service
public class YxyKeChengServiceImpl implements YxyKeChengService {

    @Autowired
    JdbcTemplate JdbcTemplate;

    @Autowired
    private YxyLessonsTopicLessonRepository yxyLessonsTopicLessonRepository;

    @Autowired
    private YxyLessonsSynchroLessonRepository yxyLessonsSynchroLessonRepository;

    @Override
    public Map<String, Object> list(String schoolId, Integer status, Integer isVisible, Integer pageNum, String keyword, String catalogId1, String catalogId2) {
        List<Object> paramList = new ArrayList<Object>();

        StringBuffer sb1 = new StringBuffer();
        sb1.append("  select                                        ");
        sb1.append("    count(id)                                   ");
        sb1.append("  from yxy_lessons.yxy_lessons_school_lesson        ");
        sb1.append("  where school_id = ?                           ");
        sb1.append("      and status = ?                            ");

        paramList.add(schoolId);
        paramList.add(status);

        if (isVisible == 1 || isVisible == 0) {
            sb1.append("  and is_visible = ?                        ");
            paramList.add(isVisible);
        }
        if (StringUtils.isNotBlank(catalogId1) && !"all".equals(catalogId1)) {
            sb1.append("  and catalog_id1 = ?                       ");
            paramList.add(catalogId1);
        }
        if (StringUtils.isNotBlank(catalogId2) && !"all".equals(catalogId2)) {
            sb1.append("  and catalog_id2 = ?                       ");
            paramList.add(catalogId2);
        }
        if (StringUtils.isNotBlank(keyword)) {
            sb1.append("  and name like ?                           ");
            paramList.add(keyword);
        }

        // 总条数
        Integer count = JdbcTemplate.queryForObject(sb1.toString(), paramList.toArray(), Integer.class);

        StringBuffer sb = new StringBuffer();
        sb.append("  select                                     ");
        sb.append("    id,                                      ");
        sb.append("    name,                                    ");
        sb.append("    cover,                                   ");
        sb.append("    summary,                                 ");
        sb.append("    count,                                   ");
        sb.append("    duration,                                ");
        sb.append("    create_date,                             ");
        sb.append("    author,                                  ");
        sb.append("    catalog_id1,                             ");
        sb.append("    catalog_id2,                             ");
        sb.append("    last_modified_date,                      ");
        sb.append("    up_date,                                 ");
        sb.append("    down_date,                               ");
        sb.append("    reject_date,                             ");
        sb.append("    vv                                       ");
        sb.append("  from yxy_lessons.yxy_lessons_school_lesson ");
        sb.append("  where school_id = ?                        ");
        sb.append("      and status = ?                         ");

        if (isVisible == 1 || isVisible == 0) {
            sb.append("  and is_visible = ?                     ");
        }
        if (StringUtils.isNotBlank(catalogId1) && !"all".equals(catalogId1)) {
            sb.append("  and catalog_id1 = ?                    ");
        }
        if (StringUtils.isNotBlank(catalogId2) && !"all".equals(catalogId2)) {
            sb.append("  and catalog_id2 = ?                    ");
        }
        if (StringUtils.isNotBlank(keyword)) {
            sb.append("  and name like ?                        ");
        }

        // 待审核
        if (status == 1) {
            // 发布时间倒序
            sb.append(" order by create_date desc               ");
        }
        // 审核通过，并且上架
        if (status == 2 && isVisible == 1) {
            // 上架时间倒序
            sb.append(" order by up_date desc                   ");
        }
        // 审核通过，并且下架
        if (status == 2 && isVisible == 0) {
            // 下架时间倒序
            sb.append(" order by down_date desc                 ");
        }
        // 审核未通过
        if (status == 3) {
            // 拒绝时间倒序
            sb.append(" order by reject_date desc               ");
        }
        sb.append("      limit ?,?                              ");
        paramList.add((pageNum - 1) * 10);
        paramList.add(10);
        List<Map<String, Object>> list = JdbcTemplate.queryForList(sb.toString(), paramList.toArray());
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("allSize", count);
        map.put("list", list);
        return map;
    }

    @Override
    public Map<String, Object> schoolListForTea(String schoolId, String authorId, String catalogId1, String catalogId2, String keyword, String lessonStatus, Integer pageNum) {
        List<Object> paramList = new ArrayList<>();

        StringBuffer sb1 = new StringBuffer();
        sb1.append(" select                                                 ");
        sb1.append("   count(t1.id)                                         ");
        sb1.append(" from yxy_lessons.yxy_lessons_school_lesson t1          ");
        sb1.append("   left join yxy_lessons.yxy_lessons_school_catalog t2  ");
        sb1.append("     on (t1.catalog_id1 = t2.id)                        ");
        sb1.append("   left join yxy_lessons.yxy_lessons_school_catalog t3  ");
        sb1.append("     on (t1.catalog_id2 = t2.id)                        ");
        sb1.append(" where t1.school_id = ?                                 ");
        sb1.append("        and t1.status != ?                              ");
        sb1.append("        and t1.author_id = ?                            ");
        paramList.add(schoolId);
        paramList.add(-1);
        paramList.add(authorId);
        if (StringUtils.isNotBlank(catalogId1)) {
            sb1.append("    and t1.catalog_id1 = ?                          ");
            paramList.add(catalogId1);
        }
        if (StringUtils.isNotBlank(catalogId2)) {
            sb1.append("     and t1.catalog_id2 = ?                         ");
            paramList.add(catalogId2);
        }
        if (StringUtils.isNotBlank(keyword)) {
            sb1.append("     and t1.name like ?                             ");
            paramList.add(keyword);
        }
        if (StringUtils.isNotBlank(lessonStatus)) {
            // 草稿，待审核，已上架，已下架，未通过
            if (lessonStatus.equals("0") || lessonStatus.equals("1") || lessonStatus.equals("3")) {
                sb1.append(" and t1.status = ?                              ");
                paramList.add(lessonStatus);
            }
            if (lessonStatus.equals("21")) {
                sb1.append(" and t1.is_visible = ? and t1.status = ?        ");
                paramList.add(1);
                paramList.add(2);
            }
            if (lessonStatus.equals("20")) {
                sb1.append(" and t1.is_visible = ? and t1.status = ?        ");
                paramList.add(0);
                paramList.add(2);
            }
        }
        Integer allSize = JdbcTemplate.queryForObject(sb1.toString(), paramList.toArray(), Integer.class);

        StringBuffer sb2 = new StringBuffer();
        sb2.append("  select                                                 ");
        sb2.append("    t1.id,                                               ");
        sb2.append("    t1.name,                                             ");
        sb2.append("    t1.cover,                                            ");
        sb2.append("    t1.create_date,                                      ");
        sb2.append("    t1.count,                                            ");
        sb2.append("    t1.duration,                                         ");
        sb2.append("    t1.status,                                           ");
        sb2.append("    t1.is_visible,                                       ");
        sb2.append("    t2.name as catalogName1,                             ");
        sb2.append("    t3.name as catalogName2,                             ");
        sb2.append("    t1.catalog_id1,                                      ");
        sb2.append("    t1.catalog_id2                                       ");
        sb2.append(" from yxy_lessons.yxy_lessons_school_lesson t1           ");
        sb2.append("   left join yxy_lessons.yxy_lessons_school_catalog t2   ");
        sb2.append("     on (t1.catalog_id1 = t2.id)                         ");
        sb2.append("   left join yxy_lessons.yxy_lessons_school_catalog t3   ");
        sb2.append("     on (t1.catalog_id2 = t3.id)                         ");
        sb2.append(" where t1.school_id = ?                                  ");
        sb2.append("        and t1.status != ?                               ");
        sb2.append("        and t1.author_id = ?                            ");
        if (StringUtils.isNotBlank(catalogId1)) {
            sb2.append("    and t1.catalog_id1 = ?                           ");
        }
        if (StringUtils.isNotBlank(catalogId2)) {
            sb2.append("     and t1.catalog_id2 = ?                          ");
        }
        if (StringUtils.isNotBlank(keyword)) {
            sb2.append("     and t1.name like ?                              ");
        }
        if (StringUtils.isNotBlank(lessonStatus)) {
            // 草稿，待审核，已上架，已下架，未通过
            if (lessonStatus.equals("0") || lessonStatus.equals("1") || lessonStatus.equals("3")) {
                sb2.append(" and t1.status = ?                              ");
            }
            if (lessonStatus.equals("21") || lessonStatus.equals("20")) {
                sb2.append(" and t1.is_visible = ? and t1.status = ?        ");
            }
        }
        sb2.append(" order by t1.created_date desc                             ");
        sb2.append(" limit ?,?                                              ");
        paramList.add((pageNum - 1) * 10);
        paramList.add(10);
        List<Map<String, Object>> list = JdbcTemplate.queryForList(sb2.toString(), paramList.toArray());

        Map<String, Object> map = new HashMap<String, Object>();
        map.put("allSize", allSize);
        map.put("list", list);
        return map;
    }

    @Override
    public Map<String, Object> topicListForTea(String schoolId, String authorId, String catalogId1, String catalogId2, String catalogId3, String keyword, String lessonStatus, Integer pageNum) {
        List<Object> paramList = new ArrayList<>();

        StringBuffer sb1 = new StringBuffer();
        sb1.append(" select                                                         ");
        sb1.append("   t1.id,                                                       ");
        sb1.append("   t1.name,                                                     ");
        sb1.append("   t1.cover,                                                    ");
        sb1.append("   t1.create_date,                                              ");
        sb1.append("   t1.count,                                                    ");
        sb1.append("   t1.duration,                                                 ");
        sb1.append("   t1.status,                                                   ");
        sb1.append("   t1.is_visible,                                               ");
        sb1.append("   t2.name as catalogName1,                                     ");
        sb1.append("   t3.name as catalogName2,                                     ");
        sb1.append("   t4.name as catalogName3                                      ");
        sb1.append(" from yxy_lessons.yxy_lessons_topic_lesson t1                   ");
        sb1.append("   left join yxy_lessons.yxy_lessons_topic_catalog t2           ");
        sb1.append("     on (t1.catalog_id1 = t2.id)                                ");
        sb1.append("   left join yxy_lessons.yxy_lessons_topic_catalog t3           ");
        sb1.append("     on (t1.catalog_id2 = t3.id)                                ");
        sb1.append("   left join yxy_lessons.yxy_lessons_topic_catalog t4           ");
        sb1.append("     on (t1.catalog_id3 = t4.id)                                ");
        sb1.append("  where t1.school_id = ?                                        ");
        sb1.append("        and t1.status != ?                                      ");
        sb1.append("        and t1.author_id = ?                                    ");
        paramList.add(schoolId);
        paramList.add(-1);
        paramList.add(authorId);
        if (StringUtils.isNotBlank(catalogId1)) {
            sb1.append(" and t1.catalog_id1 = ?                                     ");
            paramList.add(catalogId1);
        }
        if (StringUtils.isNotBlank(catalogId2)) {
            sb1.append(" and t1.catalog_id2 = ?                                     ");
            paramList.add(catalogId2);
        }
        if (StringUtils.isNotBlank(catalogId3)) {
            sb1.append(" and t1.catalog_id3 = ?                                     ");
            paramList.add(catalogId3);
        }
        if (StringUtils.isNotBlank(keyword)) {
            sb1.append(" and t1.name like ?                                         ");
            paramList.add(keyword);
        }
        if (StringUtils.isNotBlank(lessonStatus)) {
            // 草稿，待审核，已上架，已下架，未通过
            if (lessonStatus.equals("0") || lessonStatus.equals("1") || lessonStatus.equals("3")) {
                sb1.append(" and t1.status = ?                                      ");
                paramList.add(lessonStatus);
            }
            if (lessonStatus.equals("20")) {
                sb1.append(" and t1.is_visible = ? and t1.status = ?                ");
                paramList.add(0);
                paramList.add(2);
            }
            if (lessonStatus.equals("21")) {
                sb1.append(" and t1.is_visible = ? and t1.status = ?                ");
                paramList.add(1);
                paramList.add(2);
            }
        }
        List<Map<String, Object>> list = JdbcTemplate.queryForList(sb1.toString(), paramList.toArray());
        Integer allPage = list.size();
        sb1.append(" order by t1.created_date desc                          ");
        sb1.append("  limit ?,?                                             ");
        paramList.add((pageNum - 1) * 10);
        paramList.add(10);
        List<Map<String, Object>> list2 = JdbcTemplate.queryForList(sb1.toString(), paramList.toArray());
        Map<String, Object> map = new HashMap<>();
        map.put("allPage", allPage);
        map.put("list", list2);
        return map;
    }

    @Override
    public List<Map<String, Object>> findSchoolItemList(String lessonId) {
        StringBuffer sb = new StringBuffer();
        sb.append("  select                                                         ");
        sb.append("    t1.id,                                                       ");
        sb.append("    t1.order_num                                                 ");
        sb.append("  from yxy_lessons.yxy_lessons_school_lesson_item t1             ");
        sb.append("    left join yxy_lessons.yxy_lessons_school_lesson_chapter t2   ");
        sb.append("      on (t1.chapter_id = t2.id)                                 ");
        sb.append("  where t1.lesson_id = ?                                         ");
        sb.append("     and t1.status != -1                                         ");
        sb.append("  order by t2.order_num asc, t1.created_date asc                 ");
        return JdbcTemplate.queryForList(sb.toString(), new Object[] { lessonId });
    }

    @Override
    public List<Map<String, Object>> findTopicItemList(String lessonId) {
        StringBuffer sb = new StringBuffer();
        sb.append("  select                                                         ");
        sb.append("    t1.id,                                                       ");
        sb.append("    t1.order_num                                                 ");
        sb.append("  from yxy_lessons.yxy_lessons_topic_lesson_item t1             ");
        sb.append("    left join yxy_lessons.yxy_lessons_topic_lesson_chapter t2   ");
        sb.append("      on (t1.chapter_id = t2.id)                                 ");
        sb.append("  where t1.lesson_id = ?                                         ");
        sb.append("     and t1.status != -1                                         ");
        sb.append("  order by t2.order_num asc, t1.created_date asc                 ");
        return JdbcTemplate.queryForList(sb.toString(), new Object[] { lessonId });
    }

    /**
     * 查询首页同步课程
     */
    @Override
    public List<YxyLessonsSynchroLesson> findIndexSynchroLesson() {

        List<YxyLessonsSynchroLesson> lesson = new ArrayList<YxyLessonsSynchroLesson>();

        // 查出最新三条小学课程
        List<YxyLessonsSynchroLesson> lesson1 = yxyLessonsSynchroLessonRepository.findPrimaryLesson();

        // 查出最新三条初中课程
        List<YxyLessonsSynchroLesson> lesson2 = yxyLessonsSynchroLessonRepository.findJuniorLesson();

        // 查出最新两条高中课程
        List<YxyLessonsSynchroLesson> lesson3 = yxyLessonsSynchroLessonRepository.findHighLesson();
        // 所有课程ID
        if (null != lesson1 && null != lesson2 && null != lesson3) {
            lesson.addAll(lesson1);
            lesson.addAll(lesson2);
            lesson.addAll(lesson3);
        }
        return lesson;
    }

    /**
     * 查询首页专题课程
     */
    @Override
    public List<YxyLessonsTopicLesson> findIndexTopicLesson() {

        List<YxyLessonsTopicLesson> yLessons = yxyLessonsTopicLessonRepository.findTop8ByStatusAndIsVisibleOrderByLastModifiedDateDesc(2, 1);

        return yLessons;
    }
}
